/*
EXEC SP_LOAD_PROFOMA_INVOICE_SALES_ORDER_LINE_ITEMS 1334, -1
*/
ALTER PROC SP_LOAD_PROFOMA_INVOICE_SALES_ORDER_LINE_ITEMS ( @SALESORDER_ID INT, @INVOICE_ID INT )
AS
BEGIN
 	DECLARE @RESULTS TABLE( PILI_ID INT, SALESORDER_ID INT, LINEITEM_ID INT, COOL_LINE_ITEM_NO INT, COOL_PO_LINE_ITEM_NO INT, PRODUCT_ID INT, PART_NUMBER VARCHAR(50), MKT_PART_NUMBER VARCHAR(50),
	CUSTOMER_PART_NUMBER VARCHAR(50), COOL_QTY INT, TOTAL_INVOICED_QTY INT, QTY_AVAILABLE INT, PILI_QTY_PULLED INT, COOL_UNIT_COST FLOAT, COOL_AMOUNT FLOAT, COOL_REQUESTED_DATE DATETIME, 
	COOL_BOOKING_DATE DATETIME, COOL_SCHEDULE_DATE DATETIME, COOL_REVISED_DATE DATETIME, COOL_IS_CANCEL BIT, COOL_STATUS VARCHAR(50), COOL_TAXABLE BIT, COOL_DESCRIPTION VARCHAR(200),
	CUSTOMER_LABEL_NUMBER VARCHAR(50), PARENT_PART_NUMBER VARCHAR(50) )

	DECLARE @LINEITEM_ID		INT
 	DECLARE @INVOICED_QTY 		INT
 	DECLARE @QTY_TO_BE_INVOICED 	INT
	DECLARE @PILI_ID		INT /* PROFORMA INVOICE LINE ITEM ID */
	DECLARE @PILI_QTY_PULLED	INT

	DECLARE LINE_ITEM_CURSOR CURSOR FOR 
	SELECT DISTINCT COOL.LINEITEM_ID, PILI_ID, ISNULL( PILI.QTY_PULLED, 0) FROM CUSTOMER_OPEN_ORDER_LINEITEM COOL
	LEFT OUTER JOIN PROFORMA_INVOICE_LINE_ITEM PILI ON COOL.LINEITEM_ID = PILI.LINEITEM_ID AND PILI.PROFORMA_INVOICE_ID = @INVOICE_ID
	--LEFT OUTER JOIN PROFORMA_INVOICE PI ON PILI.PROFORMA_INVOICE_ID = PI.PROFORMA_INVOICE_ID AND PI.STATUS <> 'CANCELLED'
	WHERE COOL.SALESORDER_ID = @SALESORDER_ID AND ( COOL_STATUS = 'APPROVED' OR COOL_STATUS = 'HOLD' ) AND COOL_IS_CANCEL = 1
   
	OPEN LINE_ITEM_CURSOR
 	FETCH NEXT FROM LINE_ITEM_CURSOR INTO @LINEITEM_ID, @PILI_ID, @PILI_QTY_PULLED 
 	WHILE @@FETCH_STATUS = 0
 	BEGIN
		
		SET @INVOICED_QTY = ( SELECT  ISNULL(SUM(QTY_PULLED), 0) FROM PROFORMA_INVOICE PI, PROFORMA_INVOICE_LINE_ITEM PILI WHERE PI.PROFORMA_INVOICE_ID = PILI.PROFORMA_INVOICE_ID AND LINEITEM_ID = @LINEITEM_ID AND PI.STATUS <> 'CANCELLED' )
 		SET @QTY_TO_BE_INVOICED  = ( SELECT  (ISNULL(SUM(COOL_QTY),0) - @INVOICED_QTY) FROM CUSTOMER_OPEN_ORDER_LINEITEM WHERE LINEITEM_ID = @LINEITEM_ID )
		
		INSERT INTO @RESULTS ( PILI_ID, SALESORDER_ID, LINEITEM_ID, COOL_LINE_ITEM_NO, COOL_PO_LINE_ITEM_NO, PRODUCT_ID, PART_NUMBER, MKT_PART_NUMBER, CUSTOMER_PART_NUMBER, COOL_QTY, TOTAL_INVOICED_QTY, QTY_AVAILABLE, PILI_QTY_PULLED, COOL_UNIT_COST, COOL_AMOUNT,
		COOL_REQUESTED_DATE, COOL_BOOKING_DATE, COOL_SCHEDULE_DATE, COOL_REVISED_DATE, COOL_IS_CANCEL, COOL_STATUS, COOL_TAXABLE, COOL_DESCRIPTION, CUSTOMER_LABEL_NUMBER, PARENT_PART_NUMBER )
		SELECT ISNULL( @PILI_ID, -1) , @SALESORDER_ID, LINEITEM_ID, COOL_LINE_ITEM_NO, COOL_PO_LINE_ITEM_NO, P.PRODUCT_ID, PART_NUMBER, P_MARKETING_PART_NUMBER, NULL/*CUST_PART_NO*/, COOL_QTY, @INVOICED_QTY/*TOT_INV_QTY*/, @QTY_TO_BE_INVOICED/*QTY_AVAIL*/, @PILI_QTY_PULLED, COOL_UNIT_COST, 
		(ISNULL( @PILI_QTY_PULLED,0)*COOL_UNIT_COST) /*COOL_AMOUNT*/,
		CONVERT (VARCHAR, COOL_REQUESTED_DATE, 101), CONVERT (VARCHAR, COOL_BOOKING_DATE, 101), CONVERT (VARCHAR, COOL_SCHEDULE_DATE, 101), CONVERT (VARCHAR, COOL_REVISED_DATE, 101), COOL_IS_CANCEL, COOL_STATUS, COOL_TAXABLE, COOL_DESCRIPTION, NULL, NULL
		FROM CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P WHERE COOL.PRODUCT_ID = P.PRODUCT_ID AND LINEITEM_ID = @LINEITEM_ID

		FETCH NEXT FROM LINE_ITEM_CURSOR INTO @LINEITEM_ID, @PILI_ID, @PILI_QTY_PULLED
	END
	CLOSE LINE_ITEM_CURSOR
 	DEALLOCATE LINE_ITEM_CURSOR

	DELETE FROM @RESULTS WHERE PILI_ID = -1 AND QTY_AVAILABLE = 0

	SELECT * FROM @RESULTS
END